﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;


namespace QLThuVien
{
    class Database
    {
        private string strError = "";
        public string Error
        {
            get { return strError; }
        }

        #region ExecuteNonQuery
        public int ExecuteNonQuery(string strQuery,
        CommandType commandType, string[] Parameters,
         object[] Values)
        {
            int efftectRecord = 0;
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = strQuery;
            sqlCommand.Connection = KetNoi.scn;
            sqlCommand.CommandType = commandType;
            SqlParameter sqlParameter;
            for (int i = 0; i < Parameters.Length; i++)
            {
                sqlParameter = new SqlParameter();
                sqlParameter.ParameterName = Parameters[i];
                sqlParameter.SqlValue = Values[i];
                sqlCommand.Parameters.Add(sqlParameter);
            }
            try
            {
                efftectRecord = sqlCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                strError = "Error: " + ex.Message;
            }

            return efftectRecord;
        }
        public int ExecuteNonQuery(string strQuery,
        CommandType commandType, string[] Parameters,
         object[] Values, SqlDbType[] sqlDbType)
        {
            int efftectRecord = 0;
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = strQuery;
            sqlCommand.Connection = KetNoi.scn;
            sqlCommand.CommandType = commandType;
            SqlParameter sqlParameter;
            for (int i = 0; i < Parameters.Length; i++)
            {
                sqlParameter = new SqlParameter();
                sqlParameter.ParameterName = Parameters[i];
                sqlParameter.SqlDbType = sqlDbType[i];
                sqlParameter.SqlValue = Values[i];
                sqlCommand.Parameters.Add(sqlParameter);
            }
            try
            {
                efftectRecord = sqlCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                strError = "Error: " + ex.Message;
            }

            return efftectRecord;
        }

        public int ExecuteNonQuery(string strQuery,
        CommandType commandType, string[] Parameters,
         string[] Values)
        {
            int efftectRecord = 0;
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = strQuery;
            sqlCommand.Connection = KetNoi.scn;
            sqlCommand.CommandType = commandType;
            for (int i = 0; i < Parameters.Length; i++)
            {
                sqlCommand.Parameters.AddWithValue(
                    Parameters[i], Values[i]);
            }
            try
            {
                efftectRecord = sqlCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                strError = "Error: " + ex.Message;
            }

            return efftectRecord;
        }

        public int ExecuteNonQuery(string strQuery, CommandType commandType)
        {
            int efftectRecord = 0;
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = strQuery;
            sqlCommand.Connection = KetNoi.scn;
            sqlCommand.CommandType = commandType;
            try
            {
                efftectRecord = sqlCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                strError = "Error: " + ex.Message;
            }

            return efftectRecord;
        }
        public int ExecuteNonQuery(string strQuery,
        CommandType commandType, string[] Parameters,
         object[] Values, int ReturnParameter)
        {
            int efftectRecord = 0;
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = strQuery;
            sqlCommand.Connection = KetNoi.scn;
            sqlCommand.CommandType = commandType;
            SqlParameter sqlParameter;
            for (int i = 0; i < Parameters.Length; i++)
            {

                if (i == ReturnParameter)
                {
                    sqlParameter = new SqlParameter(Parameters[i], SqlDbType.Int);
                    sqlParameter.Direction =
                    ParameterDirection.ReturnValue;
                }
                else
                {
                    sqlParameter = new SqlParameter();
                    sqlParameter.ParameterName = Parameters[i];
                    sqlParameter.SqlValue = Values[i];
                }
                sqlCommand.Parameters.Add(sqlParameter);
            }
            try
            {
                sqlCommand.ExecuteNonQuery();
                efftectRecord = (int)sqlCommand.Parameters[Parameters[ReturnParameter]].Value;
            }
            catch (Exception ex)
            {
                strError = "Error: " + ex.Message;
            }

            return efftectRecord;
        }
        #endregion

        #region ExecuteScalar
        public object ExecuteScalar(string strQuery, CommandType commandType)
        {
            object objValue = null;
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = strQuery;
            sqlCommand.Connection = KetNoi.scn;
            sqlCommand.CommandType = commandType;
            try
            {
                objValue = sqlCommand.ExecuteScalar();
            }
            catch (Exception ex)
            {
                strError = "Error: " + ex.Message;
            }

            return objValue;
        }

        public object ExecuteScalar(string strQuery,
         CommandType commandType, string[] Parameters,
         object[] Values)
        {
            object objValue = null;
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = strQuery;
            sqlCommand.Connection = KetNoi.scn;
            sqlCommand.CommandType = commandType;
            SqlParameter sqlParameter;
            for (int i = 0; i < Parameters.Length; i++)
            {
                sqlParameter = new SqlParameter();
                sqlParameter.ParameterName = Parameters[i];
                sqlParameter.SqlValue = Values[i];
                sqlCommand.Parameters.Add(sqlParameter);
            }
            try
            {
                objValue = sqlCommand.ExecuteScalar();
            }
            catch (Exception ex)
            {
                strError = "Error: " + ex.Message;
            }

            return objValue;
        }
        public object ExecuteScalar(string strQuery,
         CommandType commandType, string[] Parameters,
         object[] Values, SqlDbType[] sqlDbType)
        {
            object objValue = null;
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = strQuery;
            sqlCommand.Connection = KetNoi.scn;
            sqlCommand.CommandType = commandType;
            for (int i = 0; i < Parameters.Length; i++)
            {
                sqlCommand.Parameters.AddWithValue(
                    Parameters[i], Values[i]);
                sqlCommand.Parameters[
                    i].SqlDbType = sqlDbType[i];
            }
            try
            {
                objValue = sqlCommand.ExecuteScalar();
            }
            catch (Exception ex)
            {
                strError = "Error: " + ex.Message;
            }

            return objValue;
        }
        #endregion

        #region ExecuteReader
        public ArrayList GetArrayLists(string strSQL,
            CommandType commandType, string[] Parameters,
         string[] Values, int[] NextResult)
        {

            ArrayList arrayList = new ArrayList();
            try
            {
                SqlCommand sqlCommand = new SqlCommand();

                sqlCommand.CommandText = strSQL.ToString();
                sqlCommand.Connection = KetNoi.scn;
                sqlCommand.CommandType = commandType;
                int i = 0;
                SqlParameter sqlParameter;
                for (i = 0; i < Parameters.Length; i++)
                {
                    sqlParameter = new SqlParameter();
                    sqlParameter.ParameterName = Parameters[i];
                    sqlParameter.SqlValue = Values[i];
                    sqlCommand.Parameters.Add(sqlParameter);
                }
                SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                i = 0; NextResult[0] = 1;
                object[] info;
                while (sqlDataReader.Read())
                {
                    info = new object[sqlDataReader.FieldCount];
                    sqlDataReader.GetValues(info);
                    arrayList.Add(info);
                    i++;
                }
                while (sqlDataReader.NextResult())
                {
                    NextResult[0] = i;
                    while (sqlDataReader.Read())
                    {
                        info = new object[sqlDataReader.FieldCount];
                        sqlDataReader.GetValues(info);
                        arrayList.Add(info);
                        i++;
                    }
                }
                sqlDataReader.Close();
                sqlDataReader.Dispose();
            }
            catch (Exception ex)
            {
                strError = "Error: " + ex.Message;
            }

            return arrayList;
        }
        public ArrayList GetArrayLists(string strSQL,
            CommandType commandType, string[] Parameters,
         string[] Values)
        {
            ArrayList arrayList = new ArrayList();
            try
            {
                SqlCommand sqlCommand = new SqlCommand(
                    strSQL, KetNoi.scn);
                sqlCommand.CommandType = commandType;

                SqlParameter sqlParameter;
                for (int i = 0; i < Parameters.Length; i++)
                {
                    sqlParameter = new SqlParameter();
                    sqlParameter.ParameterName = Parameters[i];
                    sqlParameter.SqlValue = Values[i];
                    sqlCommand.Parameters.Add(sqlParameter);
                }
                SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                while (sqlDataReader.Read())
                {
                    object[] info = new object[sqlDataReader.FieldCount];
                    sqlDataReader.GetValues(info);
                    arrayList.Add(info);
                }
                sqlDataReader.Close();
                sqlDataReader.Dispose();
            }
            catch (Exception ex)
            {
                strError = "Error: " + ex.Message;
            }

            return arrayList;
        }
        public ArrayList GetArrayLists(string strSQL,
            CommandType commandType, string[] Parameters,
         string[] Values, SqlDbType[] sqlDbType)
        {
            ArrayList arrayList = new ArrayList();
            try
            {
                SqlCommand sqlCommand = new SqlCommand(
                    strSQL, KetNoi.scn);
                sqlCommand.CommandType = commandType;

                SqlParameter sqlParameter;
                for (int i = 0; i < Parameters.Length; i++)
                {
                    sqlParameter = new SqlParameter();
                    sqlParameter.ParameterName = Parameters[i];
                    sqlParameter.SqlValue = Values[i];
                    sqlParameter.SqlDbType = sqlDbType[i];
                    sqlCommand.Parameters.Add(sqlParameter);
                }
                SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                while (sqlDataReader.Read())
                {
                    object[] info = new object[sqlDataReader.FieldCount];
                    sqlDataReader.GetValues(info);
                    arrayList.Add(info);
                }
                sqlDataReader.Close();
                sqlDataReader.Dispose();
            }
            catch (Exception ex)
            {
                strError = "Error: " + ex.Message;
            }

            return arrayList;
        }
        public ArrayList GetArrayLists(string strQuery,
            CommandType commandType)
        {
            ArrayList arrayList = new ArrayList();
            try
            {
                SqlCommand sqlCommand = new SqlCommand(
                    strQuery, KetNoi.scn);
                sqlCommand.CommandType = commandType;
                SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                while (sqlDataReader.Read())
                {
                    object[] objs = new object[sqlDataReader.FieldCount];
                    sqlDataReader.GetValues(objs);
                    arrayList.Add(objs);
                }
                sqlDataReader.Close();
                sqlDataReader.Dispose();
            }
            catch (Exception ex)
            {
                strError = "Error: " + ex.Message;
            }

            return arrayList;
        }


        public object[] GeObjects(string strQuery,
            CommandType commandType)
        {
            object[] info = null;
            try
            {
                SqlCommand sqlCommand = new SqlCommand();
                sqlCommand.CommandText = strQuery;
                sqlCommand.Connection = KetNoi.scn;
                sqlCommand.CommandType = commandType;
                SqlDataReader sqlDataReader;
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader.Read())
                {
                    info = new object[sqlDataReader.FieldCount];
                    sqlDataReader.GetValues(info);
                }
                sqlDataReader.Close();
                sqlDataReader.Dispose();
            }
            catch (Exception ex)
            {
                strError = "Error: " + ex.Message;

            }

            return info;
        }


        public object[] GeObjects(string strQuery,
            CommandType commandType, string[] Parameters,
         string[] Values)
        {
            object[] objValue = null;
            try
            {
                SqlCommand sqlCommand = new SqlCommand();
                sqlCommand.CommandText = strQuery;
                sqlCommand.Connection = KetNoi.scn;
                sqlCommand.CommandType = commandType;
                int i = 0;
                SqlParameter sqlParameter;
                for (i = 0; i < Parameters.Length; i++)
                {
                    sqlParameter = new SqlParameter();
                    sqlParameter.ParameterName = Parameters[i];
                    sqlParameter.SqlValue = Values[i];
                    sqlCommand.Parameters.Add(sqlParameter);
                }

                SqlDataReader sqlDataReader;
                sqlDataReader = sqlCommand.ExecuteReader();
                i = sqlDataReader.FieldCount;
                if (sqlDataReader.Read())
                {
                    objValue = new object[i];
                    sqlDataReader.GetValues(objValue);
                }
                sqlDataReader.Close();
                sqlDataReader.Dispose();
            }
            catch (Exception ex)
            {
                strError = "Error: " + ex.Message;

            }

            return objValue;
        }
        public object[] GeObjects(string strQuery,
            CommandType commandType, string[] Parameters,
         string[] Values, SqlDbType[] sqlDbType)
        {
            object[] info = null;
            try
            {
                SqlCommand sqlCommand = new SqlCommand();
                sqlCommand.CommandText = strQuery;
                sqlCommand.Connection = KetNoi.scn;
                sqlCommand.CommandType = commandType;
                int i = 0;
                for (i = 0; i < Parameters.Length; i++)
                {
                    sqlCommand.Parameters.AddWithValue(
                    Parameters[i], Values[i]);
                    sqlCommand.Parameters[
                        i].SqlDbType = sqlDbType[i];
                }

                SqlDataReader sqlDataReader;
                sqlDataReader = sqlCommand.ExecuteReader();
                i = sqlDataReader.FieldCount;

                if (sqlDataReader.Read())
                {
                    info = new object[i];
                    sqlDataReader.GetValues(info);
                }
                sqlDataReader.Close();
                sqlDataReader.Dispose();
            }
            catch (Exception ex)
            {
                strError = "Error: " + ex.Message;

            }

            return info;
        }

        #endregion

        #region DataSet
        public DataSet FillDataSet(string strParentQuery,
            string parentMember, string strChildQuery,
             string childMember,
            CommandType commandType)
        {
            DataSet dataSet = new DataSet();
            try
            {
                SqlCommand sqlCommand = new SqlCommand();
                sqlCommand.CommandText = strParentQuery;
                sqlCommand.CommandType = commandType;
                sqlCommand.Connection =
                    KetNoi.scn;
                SqlDataAdapter sqlDataAdapter =
                    new SqlDataAdapter(sqlCommand);
                sqlDataAdapter.Fill(dataSet, parentMember);

                sqlCommand = new SqlCommand();
                sqlCommand.CommandText = strChildQuery;
                sqlCommand.CommandType = commandType;
                sqlCommand.Connection =
                    KetNoi.scn;
                sqlDataAdapter =
                    new SqlDataAdapter(sqlCommand);
                sqlDataAdapter.Fill(dataSet, childMember);

                sqlDataAdapter.Dispose();
            }
            catch (Exception ex)
            {
                strError = "Error: " + ex.Message;
            }
            return dataSet;

        }

        public DataSet FillDataSet(string strQuery,
            CommandType commandType)
        {
            DataSet dataSet = new DataSet();
            try
            {
                SqlCommand sqlCommand = new SqlCommand();
                sqlCommand.CommandText = strQuery;
                sqlCommand.CommandType = commandType;
                sqlCommand.Connection =
                    KetNoi.scn;
                SqlDataAdapter sqlDataAdapter =
                    new SqlDataAdapter(sqlCommand);
                sqlDataAdapter.Fill(dataSet);
                sqlDataAdapter.Dispose();
            }
            catch (Exception ex)
            {
                strError = "Error: " + ex.Message;
            }
            return dataSet;

        }
        public DataSet FillDataSet(string strQuery,
            CommandType commandType, string[] Parameters,
            string[] Values)
        {
            DataSet dataSet = new DataSet();
            try
            {
                SqlCommand sqlCommand = new SqlCommand();
                sqlCommand.CommandText = strQuery;
                sqlCommand.CommandType = commandType;
                sqlCommand.Connection = KetNoi.scn;
                SqlParameter sqlParameter;
                for (int i = 0; i < Parameters.Length; i++)
                {
                    sqlParameter = new SqlParameter();
                    sqlParameter.ParameterName = Parameters[i];
                    sqlParameter.SqlValue = Values[i];
                    sqlCommand.Parameters.Add(sqlParameter);
                }
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
                sqlDataAdapter.Fill(dataSet);
                sqlDataAdapter.Dispose();
            }
            catch (Exception ex)
            {
                strError = "Error: " + ex.Message;
            }
            return dataSet;

        }

        public int UpdateDataSet(string strStoreProcedure,
            CommandType commandType, string[] Parameters,
            string[] Values, DataSet dataSet)
        {
            int effectRecord = 0;
            try
            {
                SqlCommand sqlCommand = new SqlCommand();
                sqlCommand.CommandText = strStoreProcedure;
                sqlCommand.CommandType = commandType;
                sqlCommand.Connection = KetNoi.scn;
                SqlParameter sqlParameter;
                for (int i = 0; i < Parameters.Length; i++)
                {
                    sqlParameter = new SqlParameter();
                    sqlParameter.ParameterName = Parameters[i];
                    sqlParameter.SqlValue = Values[i];
                    sqlCommand.Parameters.Add(sqlParameter);
                }
                SqlDataAdapter sqlDataAdapter =
                    new SqlDataAdapter(sqlCommand);
                SqlCommandBuilder sqlCommandBuilder =
                    new SqlCommandBuilder(sqlDataAdapter);
                effectRecord = sqlDataAdapter.Update(dataSet);
                sqlDataAdapter.Dispose();
            }
            catch (Exception ex)
            {
                strError = "Error: " + ex.Message;
            }
            return effectRecord;

        }
        public int UpdateDataSet(string strStoreProcedure,
            CommandType commandType, DataSet dataSet)
        {
            int effectRecord = 0;
            try
            {
                SqlCommand sqlCommand = new SqlCommand();
                sqlCommand.CommandText = strStoreProcedure;
                sqlCommand.CommandType = commandType;
                sqlCommand.Connection = KetNoi.scn;

                SqlDataAdapter sqlDataAdapter =
                    new SqlDataAdapter(sqlCommand);
                SqlCommandBuilder sqlCommandBuilder =
                    new SqlCommandBuilder(sqlDataAdapter);
                effectRecord = sqlDataAdapter.Update(dataSet);
                sqlDataAdapter.Dispose();
            }
            catch (Exception ex)
            {
                strError = "Error: " + ex.Message;
            }
            return effectRecord;

        }
        #endregion

        #region DataTableReader

        public DataTableReader FillDataTableReader(string strQuery,
           CommandType commandType)
        {
            DataTableReader dataTableReader = null;

            try
            {
                DataTable dataTable = new DataTable();
                SqlCommand sqlCommand = new SqlCommand();
                sqlCommand.CommandText = strQuery;
                sqlCommand.CommandType = commandType;
                sqlCommand.Connection = KetNoi.scn;
                SqlDataAdapter sqlDataAdapter =
                    new SqlDataAdapter(sqlCommand);
                sqlDataAdapter.Fill(dataTable);
                sqlDataAdapter.Dispose();
                dataTableReader = dataTable.CreateDataReader();
            }
            catch (Exception ex)
            {
                strError = "Error: " + ex.Message;
            }
            return dataTableReader;

        }
        #endregion

        #region DataTable
        public DataTable FillDataTable(string strQuery,
            CommandType commandType)
        {
            DataTable dataTable = new DataTable();
            try
            {
                SqlCommand sqlCommand = new SqlCommand();
                sqlCommand.CommandText = strQuery;
                sqlCommand.CommandType = commandType;
                sqlCommand.Connection = KetNoi.scn;
                SqlDataAdapter sqlDataAdapter =
                    new SqlDataAdapter(sqlCommand);
                sqlDataAdapter.Fill(dataTable);
                sqlDataAdapter.Dispose();
            }
            catch (Exception ex)
            {
                strError = "Error: " + ex.Message;
            }
            return dataTable;

        }
        public DataTable FillDataTable(string strQuery,
            CommandType commandType,
            string[] Parameters, string[] Values)
        {
            DataTable dataTable = new DataTable();
            try
            {
                SqlCommand sqlCommand = new SqlCommand();
                sqlCommand.CommandText = strQuery;
                sqlCommand.CommandType = commandType;
                sqlCommand.Connection = KetNoi.scn;
                SqlParameter sqlParameter;
                for (int i = 0; i < Parameters.Length; i++)
                {
                    sqlParameter = new SqlParameter();
                    sqlParameter.ParameterName = Parameters[i];
                    sqlParameter.SqlValue = Values[i];
                    sqlCommand.Parameters.Add(sqlParameter);
                }
                SqlDataAdapter sqlDataAdapter =
                    new SqlDataAdapter(sqlCommand);
                sqlDataAdapter.Fill(dataTable);
                sqlDataAdapter.Dispose();
            }
            catch (Exception ex)
            {
                strError = "Error: " + ex.Message;
            }
            return dataTable;

        }
        public int UpdateDataTable(
            string strStoreProcedure,
            CommandType commandType, DataTable dataTable)
        {
            int effectRecord = 0;
            try
            {
                SqlCommand sqlCommand = new SqlCommand();
                sqlCommand.CommandText = strStoreProcedure;
                sqlCommand.CommandType = commandType;
                sqlCommand.Connection = KetNoi.scn;
                
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);

                SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder(sqlDataAdapter);
                effectRecord = sqlDataAdapter.Update(dataTable);
                sqlDataAdapter.Dispose();
            }
            catch (Exception ex)
            {
                strError = "Error: " + ex.Message;
            }
            return effectRecord;

        }
        public int UpdateDataTable(string strStoreProcedure,
             CommandType commandType, string[] Parameters,
             string[] Values, DataTable dataTable)
        {
            int effectRecord = 0;
            try
            {
                SqlCommand sqlCommand = new SqlCommand();
                sqlCommand.CommandText = strStoreProcedure;
                sqlCommand.CommandType = commandType;
                sqlCommand.Connection = KetNoi.scn;
                SqlParameter sqlParameter;
                for (int i = 0; i < Parameters.Length; i++)
                {
                    sqlParameter = new SqlParameter();
                    sqlParameter.ParameterName = Parameters[i];
                    sqlParameter.SqlValue = Values[i];
                    sqlCommand.Parameters.Add(sqlParameter);
                }
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);

                SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder(sqlDataAdapter);
                effectRecord = sqlDataAdapter.Update(dataTable);
                sqlDataAdapter.Dispose();
            }
            catch (Exception ex)
            {
                strError = "Error: " + ex.Message;
            }
            return effectRecord;

        }
        #endregion
    }
}
